﻿

create proc dbo.CreateSaveProcs (
	@TableName	varchar(150),
	@ProcName	varchar(150)
)
as
begin

	declare	@CreateText		varchar(8000) = '',
			@CheckText		varchar(8000) = '',
			@WithSelectText	varchar(8000) = '',
			@MergeText		varchar(8000) = '',
			@UpdateText		varchar(8000) = '',
			@InsertText		varchar(8000) = '',
			@InsertMiddleText	varchar(8000) = '',
			@ValuesText		varchar(8000) = '',
			@EndText		varchar(8000) = ''
			
	declare	@FieldName		varchar(150) = '',
			@FieldType		varchar(150) = '',
			@ColumnID		smallint = 0,
			@Pk			bit = 0,
			@SystemTypeID	smallint = 0,
			@CountPk		smallint = 0
			
			
	set @CreateText = 'create proc ' + @ProcName + ' (' + char(13)
	
	set @WithSelectText = char(9) + 'with src' + char(13) + char(9) + 'as' + char(13) + char(9) + '(' +char(13) + char(9) + char(9) + 'select' +char(13)
	
	set @MergeText = char(9) + 'merge ' + @TableName + ' as target' + char(13)
		+ char(9) + 'using src' + char(13) + char(9) + char(9) + 'on (target.' 
		
	set @UpdateText = char(9) + 'when matched then' + char(13) + char(9) + char(9) + 'update SET' + char(13) 
	
	set @InsertText = char(9) + 'when not matched by target then' + char(13) + char(9) + char(9) + 'insert (' + char(13)
	
	set @InsertMiddleText =  + char(9) + char(9) + ')' + char(13)  + char(9) + char(9) + 'values (' + char(13)
	
	declare cFields cursor for
	select a.[name], 
		a.system_type_id, 
		(case when ic.column_id is null then 0 else 1 end) 'Pk',
		t.name + 
			case 
				when isnull(a.collation_name,'') <> '' then '(' + cast((a.max_length / 2) as varchar(20)) + ')'
				when a.system_type_id = 106 then '(' + cast(a.precision as varchar(20)) + ',' + cast(a.scale as varchar(20)) + ')'
				else ''
			end 'FieldType'
	from sys.all_columns a
		left join sys.index_columns ic
			on a.[object_id] = ic.[object_id] 
				and a.column_id = ic.column_id
		inner join sys.types t
			on a.system_type_id = t.system_type_id
	where a.[object_id] = (select id from sysobjects where name = @TableName)
	order by a.column_id
	
	open cFields
	
	fetch next from cFields
	into @FieldName, @SystemTypeID, @Pk, @FieldType
	
	while @@fetch_status = 0
	begin
		if (@Pk != 0)
		begin
			set @CountPk += 1
			set @MergeText +=  (case when @CountPk > 1 then + char(13) + char(9) + char(9) + 'and ' else '' end) + @FieldName + ' = src.' + @FieldName 
		end
		
		set @CreateText += char(9) + '@' + @FieldName + ' ' + @FieldType + ',' + char(13)
		
		set @CheckText += char(9) + 'if @' + @FieldName + ' is null' + char(13)
		set @CheckText += char(9) + 'begin' + char(13)
		set @CheckText += char(9) + char(9) + 'set @ErrorMessage = ''' + @FieldName + ' is null''' + char(13)
		set @CheckText += char(9) + char(9) + 'goto EndWithError' + char(13)
		set @CheckText += char(9) + 'end' + char(13)
		
		set @WithSelectText += char(9) + char(9) + char(9) + '@' + @FieldName + ' ''' + @FieldName + '''' + ',' + char(13) 
		
		set @UpdateText += char(9) + char(9) + char(9) + @FieldName + ' = src.' + @FieldName + ',' + char(13)
		
		set @InsertText += char(9) + char(9) + char(9) + @FieldName + ',' + char(13)
		
		set @ValuesText += char(9) + char(9) + char(9) + 'src.' + @FieldName + ',' + char(13)
		
		
		
		fetch next from cFields
		into @FieldName, @SystemTypeID, @Pk, @FieldType
	end
	close cFields
	deallocate cFields
	
	set @CreateText += char(9) + '@ErrorMessage	nvarchar(400) output' + char(13) + ')' + char(13)
	set @CreateText += 'as' + char(13) + 'begin' + char(13)
	
	set @CheckText += char(13) + char(9) + 'begin tran;' + char(13) + char(13)
	
	set @WithSelectText = left(@WithSelectText, len(@WithSelectText) - 2) + char(13)
	
	set @WithSelectText += char(13) + char(9) + ')' + char(13) + char(13)
	
	set @InsertText = left(@InsertText, len(@InsertText) - 2) + char(13)
	
	set @MergeText += ')' + char(13)
	
	set @UpdateText = left(@UpdateText, len(@UpdateText) - 2) + char(13)
	
	set @ValuesText = left(@ValuesText, len(@ValuesText) - 2) + char(13)
	
	set @ValuesText += char(9) + char(9) + ');' + char(13) + char(13)
	
	set @EndText = char(9) + 'commit tran' + char(13) + char(9) + 'return 0' + char(13) + char(13) + 'EndWithError:' +char(13)
	set @EndText += char(9) + 'RAISERROR ( @ErrorMessage, 16, 1)' + char(13) + char(9) + 'return 1' + char(13) + 'end'
	
	set @CreateText += @CheckText + @WithSelectText + @MergeText + @UpdateText + @InsertText + @InsertMiddleText + @ValuesText + @EndText
	
	print @CreateText
end




GO
GRANT EXECUTE
    ON OBJECT::[dbo].[CreateSaveProcs] TO PUBLIC
    AS [dbo];

